package com.kedacom.freedomLabel.utils;

import android.support.annotation.NonNull;
import android.text.TextUtils;

import com.kedacom.freedomLabel.excel.AdditionalColumn;
import com.kedacom.freedomLabel.excel.IRowRecord;
import com.kedacom.freedomLabel.excel.ISheetRecord;
import com.kedacom.freedomLabel.excel.ITableRecord;
import com.seray.scaleviewlib.utils.LogUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.List;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * Author：李程
 * CreateTime：2019/4/16 14:16
 * E-mail：licheng@kedacom.com
 * Describe：实体转Excel帮助类
 */
public class ExcelUtils {

    private static final String TAG = ExcelUtils.class.getSimpleName();

    private final static String UTF8_ENCODING = "UTF-8";
    private static final int ROW_HEIGHT = 350;
    private static final int COLUMN_BASE_WIDTH = 10;
    private static WritableCellFormat arial14format = null;
    private static WritableCellFormat arial10format = null;
    private static WritableCellFormat arial12format = null;

    public static void toExcel(@NonNull String filePath, @NonNull ITableRecord data) {
        String tableName = data.getTableName();
        String fileName = filePath + "/" + tableName;
        initExcel(fileName, data);
        ISheetRecord[] tableSheets = data.getTableSheets();
        for (ISheetRecord tableSheet : tableSheets) {
            String sheetName = tableSheet.sheetName();
            List<? extends IRowRecord> rowRecords = tableSheet.getRowRecords();
            writeToExcel(fileName, sheetName, rowRecords, tableSheet.additionalColumnNames());
        }
    }

    private static void initExcel(String fileName, ITableRecord tableRecord) {
        format();
        ISheetRecord[] tableSheets = tableRecord.getTableSheets();
        File file = createNewFile(fileName);
        WritableWorkbook workbook = null;
        try {
            workbook = Workbook.createWorkbook(file);
            for (int sheetIndex = 0; sheetIndex < tableSheets.length; sheetIndex++) {
                ISheetRecord tableSheet = tableSheets[sheetIndex];
                String sheetName = tableSheet.sheetName();
                String[] columnNames = tableSheet.columnNames();
                AdditionalColumn[] additionalColumnNames = tableSheet.additionalColumnNames();
                WritableSheet sheet = workbook.createSheet(sheetName, sheetIndex);
                sheet.addCell(new Label(0, 0, fileName, arial14format));
                // 如果有额外的列，则添加
                for (int col = 0; col < columnNames.length + additionalColumnNames.length; col++) {
                    if (col < columnNames.length) {
                        sheet.addCell(new Label(col, 0, columnNames[col], arial10format));
                    } else {
                        sheet.addCell(new Label(col, 0, additionalColumnNames[col - columnNames.length].getColumnName(), arial10format));
                    }
                }
                sheet.setRowView(sheetIndex, ROW_HEIGHT);
            }
            workbook.write();
        } catch (Exception e) {
            LogUtils.e(TAG, e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    LogUtils.e(TAG, e);
                }
            }
        }
    }

    private static void writeToExcel(String fileName, String sheetName, List<? extends IRowRecord> data, AdditionalColumn[] additionalColumns) {
        WritableWorkbook writeBook = null;
        InputStream in = null;
        try {
            WorkbookSettings setEncode = new WorkbookSettings();
            setEncode.setEncoding(UTF8_ENCODING);
            in = new FileInputStream(new File(fileName));
            Workbook workbook = Workbook.getWorkbook(in);
            writeBook = Workbook.createWorkbook(new File(fileName), workbook);
            WritableSheet sheet = writeBook.getSheet(sheetName);
            for (int dataPosition = 0; dataPosition < data.size(); dataPosition++) {
                IRowRecord record = data.get(dataPosition);
                String[] rowValues = record.rowValues();
                boolean[] isNumber = record.isNumber();

                // 使用isNumber进行格式化
                boolean useNumberFormat = isNumber != null && isNumber.length > 0 && isNumber.length == rowValues.length;

                for (int i = 0; i < rowValues.length + additionalColumns.length; i++) {
                    String rowValue = null;

                    if (i < rowValues.length) {
                        rowValue = rowValues[i];
                        if(useNumberFormat && isNumber[i]){  // 转化为数字
                            sheet.addCell(new Number(i, dataPosition + 1, Double.parseDouble(rowValue), arial12format));
                        }else {
                            sheet.addCell(new Label(i, dataPosition + 1, rowValue, arial12format));
                        }

                    } else {
                        // 反射获取IRowRecord的中给定属性的值
                        try {
                            Field field = record.getClass().getDeclaredField(additionalColumns[i - rowValues.length].getColumnPropertyName());
                            rowValue = String.valueOf(field.get(record)); // 如果数据指定的属性是对象则需要重写数据的toString方法
                        } catch (Exception e) {
                            LogUtils.e(e.getMessage());
                            e.printStackTrace();
                        }

                        // additionalColumns 暂不支持格式化成数字
                        sheet.addCell(new Label(i, dataPosition + 1, rowValue, arial12format));
                    }

                    if (rowValue != null) {
                        sheet.setColumnView(i, rowValue.length() + COLUMN_BASE_WIDTH);
                    }
                }
                sheet.setRowView(dataPosition + 1, ROW_HEIGHT);
            }
            writeBook.write();
        } catch (Exception e) {
            LogUtils.e(TAG, e);
        } finally {
            try {
                if (writeBook != null) {
                    writeBook.close();
                }
                if (in != null) {
                    in.close();
                }
            } catch (Exception e) {
                LogUtils.e(TAG, e);
            }
        }
    }

//    private static void initExcel(String fileName, String sheetName, String[] colName) {
//        format();
//        WritableWorkbook workbook = null;
//        try {
//            File file = createNewFile(fileName);
//            workbook = Workbook.createWorkbook(file);
//            WritableSheet sheet = workbook.createSheet(sheetName, 0);
//            sheet.addCell(new Label(0, 0, fileName, arial14format));
//            for (int col = 0; col < colName.length; col++) {
//                sheet.addCell(new Label(col, 0, colName[col], arial10format));
//            }
//            sheet.setRowView(0, ROW_HEIGHT);
//            workbook.write();
//        } catch (Exception e) {
//            LogUtils.e(TAG, e);
//        } finally {
//            if (workbook != null) {
//                try {
//                    workbook.close();
//                } catch (Exception e) {
//                    LogUtils.e(TAG, e);
//                }
//            }
//        }
//    }

    /**
     * 单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...
     */
    public static void format() {
        try {
            WritableFont arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
            arial14font.setColour(Colour.LIGHT_BLUE);
            arial14format = new WritableCellFormat(arial14font);
            arial14format.setAlignment(jxl.format.Alignment.CENTRE);
            arial14format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            arial14format.setBackground(Colour.VERY_LIGHT_YELLOW);

            WritableFont arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            arial10format = new WritableCellFormat(arial10font);
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);
            arial10format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            arial10format.setBackground(Colour.GRAY_25);

            WritableFont arial12font = new WritableFont(WritableFont.ARIAL, 10);
            arial12format = new WritableCellFormat(arial12font);
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);//对齐格式
            arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); //设置边框

        } catch (WriteException e) {
            LogUtils.e(TAG, e.getCause());
        }
    }

    /**
     * 创建文件及父文件夹
     */
    private static File createNewFile(String fileName) {
        File file = new File(fileName);
        File parFile = file.getParentFile();
        if (!parFile.exists()) {
            parFile.mkdirs();
        }
        if (!file.exists()) {
            try {
                file.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
                LogUtils.e(TAG, e);
            }
        }
        return file;
    }
}
